package Dao;


import pojo.User;
import util.C3P0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class UserDao03 {

    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;


  /*  public UserDao02() {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
*/

    //改

    public int update(User user_old, User user_new) throws SQLException {
        connection = C3P0Util.getConnection();

        int result = 0;
        String username1 = user_old.getUsername();
        String username2 = user_new.getUsername();
        String password1 = user_old.getPassword();
        String password2 = user_new.getPassword();
        String sql = "update J_user set username =?," +
                "password = ? " + "where username =? and password =?";

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username2);
        preparedStatement.setString(2, password2);
        preparedStatement.setString(3, username1);
        preparedStatement.setString(4, password1);
        result = preparedStatement.executeUpdate();


        C3P0Util.closeAll(resultSet, preparedStatement, connection);

        return result;

    }

    //删除


    public int delete(User user) throws SQLException {
        connection = C3P0Util.getConnection();
        int result = 0;

        String sql = "delete from J_user where id =?";


        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, user.getId());
        result = preparedStatement.executeUpdate();


        C3P0Util.closeAll(resultSet, preparedStatement, connection);

        return result;

    }

    //查询

    public List<User> query() throws SQLException {
        connection = C3P0Util.getConnection();

        String sql = "select * from J_user";

        List<User> userList = new ArrayList<>();
        preparedStatement = UserDao03.connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            int state = resultSet.getInt("state");
            User user = new User(id, username, password, state);
            userList.add(user);
        }

        C3P0Util.closeAll(resultSet, preparedStatement, connection);

        return userList;
    }

    //新增用户

    public int insert(User user) throws SQLException {
        connection = C3P0Util.getConnection();

        String sql = "insert into J_USER (id,username,password,state)" +
                " values (seq_j_user.nextval,?,?,?)";
        preparedStatement = connection.prepareStatement(sql);


        preparedStatement.setString(1, user.getUsername());
        preparedStatement.setString(2, user.getPassword());
        preparedStatement.setInt(3, user.getState());
        int rs = preparedStatement.executeUpdate();
        C3P0Util.closeAll(resultSet, preparedStatement, connection);
        return rs;


    }


}

